﻿///    /************************************************************************ 
///     * uoLib library for .Net projects.
///     * Copyright (c) 2009 by uonun
///     * Homepage: http://uolib.ourome.com
///     * 
///     * This program is free software: you can redistribute it and/or modify
///     * it under the terms of the GNU General Public License as published by
///     * the Free Software Foundation, either version 3 of the License, or
///     * (at your option) any later version.

///     * This program is distributed in the hope that it will be useful,
///     * but WITHOUT ANY WARRANTY; without even the implied warranty of
///     * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
///     * GNU General Public License for more details.

///     * You should have received a copy of the GNU General Public License
///     * along with this program.  If not, see http://www.gnu.org/licenses/gpl.html.
///     ***********************************************************************/ 

//#define SQL2000

using System;
using System.Configuration;
using uoLib.Exceptions;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace uoLib.Data.Singleton
{
    /// <summary>
    /// 使用 SqlClient 链接字符串的 Sql Server 数据库（2000/2005/2008均可）。
    /// 链接字符串配置名称：uoLib.SqlClientSqlServer
    /// </summary>
    /// <example>
    /// <p>uoLib类库在使用上极其方便。它为三种常用数据库类型分别提供了一个数据库单体实例，它们是：</p>
    /// <ul>
    /// <li>oledb连接的Access：<see cref="OleDbAccess"/></li>
    /// <li>SqlClient连接的SQL Server：<see cref="SqlClientSqlServer"/></li>
    /// <li>odbc连接的SQLite：<see cref="OdbcSQLite"/></li>
    /// </ul>
    /// <p>您可以在程序中直接使用这三个单体实例来访问相应的数据库。下面是示例代码：</p><p><strong>web.Config</strong></p>
    /// <code><![CDATA[
    /// <?xml version="1.0"?>
    /// <configuration>
    ///     <connectionStrings>
    ///         <remove name="*"/>
    ///         <add name="uoLib.SqlClientSqlServer" connectionString="Data Source=127.0.0.1;Initial Catalog=NorthWind;User Id=sa;Password=;"/>
    ///         <add name="uoLib.OleDbAccess" connectionString=""/>
    ///         <add name="uoLib.OdbcSQLite" connectionString="DRIVER=SQLite3 ODBC Driver;Database=files/demo.db3;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"/>
    ///     </connectionStrings>
    /// </configuration>]]></code>
    /// <p>web.config文件中可以将数据库的连接字符串留空。如上面的配置中，可以不配置uoLib.OleDbAccess的连接字符串，因为在 cs 代码中将另行写入。详见下：</p>
    /// <code><![CDATA[
    /// using System;
    /// using System.Data;
    /// using System.Data.Common;
    /// using uoLib.Data.Singleton;
    /// namespace DemoWeb  {
    ///     public partial class Demo : System.Web.UI.Page      
    ///     {
    ///         protected void Page_Load(object sender, EventArgs e)          
    ///         {               
    ///             // 使用 SqlClient 链接字符串的 Sql Server 数据库
    ///             // 2000/2005/2008均可。
    ///             // 链接字符串配置名称：uoLib.SqlClientSqlServer
    ///             SqlClientSqlServer SQL = SqlClientSqlServer.Instance;
    ///             const string TABLENAME = "Products";
    ///             DbCommand cmd = SQL.CreateCommand("SELECT TOP 1 ID FROM {0}", TABLENAME);
    ///             DataSet ds = SQL.SelectData(cmd);
    ///             if (ds == null){
    ///                 Response.Write("数据库为空！");
    ///             }else{
    ///                 Response.Write(string.Format("共 {0} 行记录。", ds.Tables[0].Rows.Count));
    ///             }
    ///             
    ///             // 使用 OleDb 链接字符串的 Access 数据库
    ///             // 97/2000/2007均可。
    ///             // 链接字符串配置名称：uoLib.OleDbAccess
    ///             OleDbAccess Access = OleDbAccess.Instance;
    ///             Access.CreateConnection(OleDbAccess.CreateExampleConnectionString(Server.MapPath("files/test.mdb"), "uonun", "ourome.com"));
    ///             Response.Write(Access.Conn.ConnectionString);
    ///             
    ///             // 使用 Odbc 链接字符串的 SQLite 数据库。
    ///             // 链接字符串配置名称：uoLib.OdbcSQLite
    ///             OdbcSQLite SQLite = OdbcSQLite.Instance;
    ///             SQLite.Conn.Open();
    ///             //do something.. 
    ///             SQLite.Conn.Close();
    ///         }
    ///     } 
    /// }]]></code>
    /// <p>注意：<ol>
    ///<li>要使用数据库的单体实例，必须在web.config里面配置相应名称的连接字符串节。（可以留空，但必须有这一节，如上例中的uoLib.OleDbAccess）</li>
    ///<li>由于使用了单体模式，因此在整个项目中，将只存在一个数据库实例，在简化了数据库使用的同时也提高了性能。不过如此一来，在整个系统中也将仅有一个相同数据库的实例。以<see cref="SqlClientSqlServer"/>为例，您无法使用<see cref="SqlClientSqlServer.Instance"/>来生成两个不同的数据库。（要在同一系统中使用多个相同数据库实例，请使用<see cref="Database"/>。）</li>
    ///</ol></p>
    /// </example>
    /// <seealso cref="OleDbAccess"/>
    /// <seealso cref="OdbcSQLite"/>
    public sealed class SqlClientSqlServer : BaseProvider
    {
        #region 单件构造
        private static SqlClientSqlServer db;
        private static readonly object padlock = new object();

        private SqlClientSqlServer()
            : base(ConnectionType.SqlClient)
        {
            this.ConnectionType = ConnectionType.SqlClient;
            connStringName = "uoLib.SqlClientSqlServer";
        }

        //单件模式的 N 种构造方式，参考：http://terrylee.cnblogs.com/archive/2005/12/09/293509.html
        /// <summary>
        /// 返回一个数据库实例（单件模式）
        /// </summary>
        /// <remarks>必须在配置文件中设置名称为“uoLib.SqlClientSqlServer”的数据库连接字符串</remarks>
        public static SqlClientSqlServer Instance
        {
            get
            {
                //确保不是每次访问都加锁
                if (db == null)
                {
                    //加锁以确保线程安全
                    lock (padlock)
                    {
                        if (db == null)
                        {
                            db = new SqlClientSqlServer();
                            try
                            {
                                db.ConnectionString = ConfigurationManager.ConnectionStrings[db.connStringName].ConnectionString;
                            }
                            catch (System.NullReferenceException)
                            {
                                throw new NullConnectionStringException(db.connStringName, db.ToString());
                            }
                            catch (Exception ex)
                            {
                                throw new Exception(ex.ToString());
                            }
                        }
                        return db;
                    }
                }
                return db;
            }
        }
        #endregion

        /// <summary>
        /// 判读数据库名是否已存在
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <returns></returns>
        public bool IsDatabaseExist(string dbName)
        {
            bool Rn = false;
            string sql = "select 1 from master.dbo.sysdatabases where name = '{0}'";
            using (DataSet ds = db.SelectData(sql, dbName))
            {
                if (ds != null)
                {
                    Rn = true;
                }
            }
            return Rn;
        }

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <returns></returns>
        public bool CreateDatabase(string dbName)
        {
            bool Rn = false;
            if (!IsDatabaseExist(dbName))
            {
                ExecuteNonQuery(db.CreateCommand("CREATE DATABASE [{0}]", dbName));
                Rn = true;
            }
            return Rn;
        }

        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <returns></returns>
        /// <exception cref="DatabaseNotExistException">指定的数据库不存在</exception>
        public bool DeleteDatabase(string dbName)
        {
            bool Rn = false;
            if (!IsDatabaseExist(dbName))
            {
                throw new DatabaseNotExistException(dbName);
            }
            else
            {
                ExecuteNonQuery(db.CreateCommand("DROP DATABASE [{0}]", dbName));
                Rn = true;
            }
            return Rn;
        }

        /// <summary>
        /// 查询数据库中已存在的所有数据库
        /// </summary>
        /// <returns></returns>
        public DataSet ListDatabases()
        {
            string sql = "select * from master.dbo.sysdatabases where name <>'master' and name <>'tempdb' and name<>'model' and name<>'msdb' and name<>'ReportServer' and name<>'ReportServerTempDB'";
            DataSet ds = db.SelectData(sql);
            return ds;
        }

        /// <summary>
        /// 获取数据库中的所有表名称
        /// </summary>
        /// <returns></returns>
        public DataSet ListTables(string dbName)
        {
#if SQL2000
            string sql = string.Format("select * from [{0}].dbo.sysobjects where xtype = 'u' order by name asc", dbName);
#else
            string sql = string.Format("select * from [{0}].sys.tables order by name asc", dbName);
#endif
            DataSet ds = db.SelectData(sql);
            return ds;
        }

        /// <summary>
        /// 获取数据库指定表的所有触发器信息
        /// </summary>
        /// <returns></returns>
        /// <remarks>tblname 为空或null时，将查询所有表的触发器</remarks>
        public DataSet ListTriggers(string dbName, string tblname)
        {
            string sql;
#if SQL2000
            if (!string.IsNullOrEmpty(tblname))
                sql = string.Format("select * from [{0}].dbo.sysobjects where xtype = 'tr' and parent_obj=OBJECT_ID(N'{0}.dbo.{1}') order by name asc", dbName, tblname);
            else
                sql = string.Format("select * from [{0}].dbo.sysobjects where xtype = 'tr' order by name asc", dbName);
#else
            if (!string.IsNullOrEmpty(tblname))
                sql = string.Format("select * from [{0}].sys.triggers where parent_id=OBJECT_ID(N'{0}.dbo.{1}') order by name asc", dbName, tblname);
            else
                sql = string.Format("select * from [{0}].sys.triggers order by name asc", dbName);
#endif
            DataSet ds = db.SelectData(sql);
            return ds;
        }

        /// <summary>
        /// 获取指定数据库的所有视图
        /// </summary>
        /// <returns></returns>
        public DataSet ListViews(string dbName)
        {
            string sql;
#if SQL2000
            sql = string.Format("select * from [{0}.dbo.sysobjects] where xtype = 'V' order by name asc", dbName);
#else
            sql = string.Format("select * from [{0}.sys.views] order by name asc", dbName);
#endif
            DataSet ds = db.SelectData(sql);
            return ds;
        }

        /// <summary>
        /// 获取数据库中指定表的信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetTableDetail(string tblname)
        {
            string sql = string.Format("select * from master.dbo.sysdatabases where name = '{0}'", tblname);
            DataSet ds = db.SelectData(sql);
            return ds;
        }

        /// <summary>
        /// 获取数据库指定表的所有列信息
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="tblname">数据表名</param>
        /// <returns></returns>
        /// <remarks>tblname 为空或null时，将查询所有表信息</remarks>
        public DataSet ListColumns(string dbName, string tblname)
        {
            StringBuilder sb = new StringBuilder();
            #region 构造SQL语句
            sb.Append(" SELECT");
            sb.Append(" TableName=O.name,");
            sb.Append(" TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),");
            sb.Append(" Column_id=C.column_id,");
            sb.Append(" ColumnName=C.name,");
            sb.Append(" PrimaryKey=ISNULL(IDX.PrimaryKey,N''),");
            sb.Append(" [IDENTITY]=CASE WHEN C.is_identity=1 THEN -1 ELSE 0 END,");
            sb.Append(" Computed=CASE WHEN C.is_computed=1 THEN -1 ELSE 0 END,");
            sb.Append(" Type=T.name,");
            sb.Append(" Length=C.max_length,");
            sb.Append(" Precision=C.precision,");
            sb.Append(" Scale=C.scale,");
            sb.Append(" NullAble=CASE WHEN C.is_nullable=1 THEN -1 ELSE 0 END,");
            sb.Append(" [Default]=ISNULL(D.definition,N''),");
            sb.Append(" ColumnDesc=ISNULL(PFD.[value],N''),");
            sb.Append(" IndexName=ISNULL(IDX.IndexName,N''),");
            sb.Append(" IndexSort=ISNULL(IDX.Sort,N''),");
            sb.Append(" Create_Date=O.Create_Date,");
            sb.Append(" Modify_Date=O.Modify_date");
            sb.Append(" FROM [{0}].sys.columns C");
            sb.Append(" INNER JOIN [{0}].sys.objects O");
            sb.Append(" ON C.[object_id]=O.[object_id]");
            sb.Append("  AND O.type='U'");
            sb.Append("  AND O.is_ms_shipped=0");
            sb.Append("  INNER JOIN [{0}].sys.types T");
            sb.Append("  ON C.user_type_id=T.user_type_id");
            sb.Append(" LEFT JOIN [{0}].sys.default_constraints D");
            sb.Append("  ON C.[object_id]=D.parent_object_id");
            sb.Append("       AND C.column_id=D.parent_column_id");
            sb.Append("        AND C.default_object_id=D.[object_id]");
            sb.Append(" LEFT JOIN [{0}].sys.extended_properties PFD");
            sb.Append("  ON PFD.class=1 ");
            sb.Append("       AND C.[object_id]=PFD.major_id ");
            sb.Append("        AND C.column_id=PFD.minor_id");
            sb.Append("  LEFT JOIN [{0}].sys.extended_properties PTB");
            sb.Append("      ON PTB.class=1 ");
            sb.Append("        AND PTB.minor_id=0 ");
            sb.Append("        AND C.[object_id]=PTB.major_id");
            sb.Append("  LEFT JOIN");
            sb.Append("(");
            sb.Append("    SELECT ");
            sb.Append("      IDXC.[object_id],");
            sb.Append("      IDXC.column_id,");
            sb.Append("      Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,IDXC.is_descending_key)");
            sb.Append("         WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE N'' END,");
            sb.Append("     PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN -1 ELSE 0 END,");
            sb.Append("      IndexName=IDX.Name");
            sb.Append("  FROM [{0}].sys.indexes IDX");
            sb.Append(" INNER JOIN [{0}].sys.index_columns IDXC");
            sb.Append("      ON IDX.[object_id]=IDXC.[object_id]");
            sb.Append("        AND IDX.index_id=IDXC.index_id");
            sb.Append("  LEFT JOIN [{0}].sys.key_constraints KC");
            sb.Append("     ON IDX.[object_id]=KC.[parent_object_id]");
            sb.Append("           AND IDX.index_id=KC.unique_index_id");
            sb.Append(" INNER JOIN");
            sb.Append("  (");
            sb.Append("    SELECT [object_id], Column_id, index_id=MIN(index_id)");
            sb.Append("    FROM [{0}].sys.index_columns");
            sb.Append("     GROUP BY [object_id], Column_id");
            sb.Append("  ) IDXCUQ");
            sb.Append("      ON IDXC.[object_id]=IDXCUQ.[object_id]");
            sb.Append("         AND IDXC.Column_id=IDXCUQ.Column_id");
            sb.Append("          AND IDXC.index_id=IDXCUQ.index_id");
            sb.Append(" ) IDX");
            sb.Append("  ON C.[object_id]=IDX.[object_id]");
            sb.Append("     AND C.column_id=IDX.column_id ");
            sb.Append("");
            if (!string.IsNullOrEmpty(tblname))
            {
                sb.Append(string.Format(" WHERE O.name=N'{0}' ", tblname));
            }
            sb.Append("ORDER BY O.name,C.column_id;");
            #endregion
            DataSet ds = db.SelectData(string.Format(sb.ToString(), dbName));
            return ds;
        }

        /// <summary>
        /// 判断数据表中是否存在指定字段
        /// </summary>
        /// <param name="dbName">数据库名称</param>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public bool IsColumnExists(string dbName, string tableName, string columnName)
        {
            string sql;
#if SQL2000
            sql = string.Format("select count(1) from [{0}].dbo.syscolumns where [id]=object_id('{0}.dbo.{1}')  and [name]='{2}'", dbName, tableName, columnName);
#else
            sql = string.Format("select COUNT(1) from [{0}].sys.columns where object_id=object_id('{0}.dbo.{1}') and [name]='{2}'", dbName, tableName, columnName);
#endif
            object res = db.ExecuteScalar(db.CreateCommand(sql));
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }

        #region 数据库备份、还原
        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="targetDatabaseConn">待备份数据库的连接</param>
        /// <param name="backupToPath">用于保存备份文件的目录的全路径（注意：必须使用数据库服务器上的路径）</param>
        ///<param name="backupFileName"></param>
        /// <returns></returns>
        /// <remarks>
        /// 必须使用目标数据库的连接字符串。如需要备份uoLibDb，则conn中的数据库必须是uoLibDb。
        /// </remarks>
        public static string BackUp(SqlConnection targetDatabaseConn, string backupToPath, string backupFileName)
        {
            string procname;
            string sql;
            string databaseName = targetDatabaseConn.Database;
            //if (string.IsNullOrEmpty(name)) name = string.Format("{0}_{1:yyyyMMddHHmmss}", databaseName, DateTime.Now);

            #region 准备备份路径
            if (Functions.IsNullOrEmptyStr(backupFileName)) throw new Exception("参数错误：必须指定备份文件名！");

            if (!backupToPath.EndsWith("/") && !backupToPath.EndsWith(@"\")) { backupToPath = backupToPath + @"\"; }
            string newFilePath = backupToPath + backupFileName + ".bak";

            System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(backupToPath);
            if (!di.Exists)
            {
                try { di.Create(); }
                catch (Exception err) { throw err; }
            }
            else
            {
                System.IO.FileInfo fi = new System.IO.FileInfo(newFilePath);
                if (fi.Exists)
                {
                    throw new Exception(string.Format("数据库服务器上文件已经存在：{0}。", newFilePath));
                }
            }
            #endregion

            targetDatabaseConn.Open();        //打开数据库连接

            #region 删除逻辑备份设备，但不会删掉备份的数据库文件
            procname = "sp_dropdevice";
            SqlCommand sqlcmd1 = new SqlCommand(procname, targetDatabaseConn);
            sqlcmd1.CommandType = CommandType.StoredProcedure;

            SqlParameter sqlpar = new SqlParameter();
            sqlpar = sqlcmd1.Parameters.Add("@logicalname", SqlDbType.VarChar, 20);
            sqlpar.Direction = ParameterDirection.Input;
            sqlpar.Value = databaseName;

            try        //如果逻辑设备不存在，略去错误
            { sqlcmd1.ExecuteNonQuery(); }
            catch { }
            #endregion

            #region 创建逻辑备份设备
            procname = "sp_addumpdevice";
            SqlCommand sqlcmd2 = new SqlCommand(procname, targetDatabaseConn);
            sqlcmd2.CommandType = CommandType.StoredProcedure;

            sqlpar = sqlcmd2.Parameters.Add("@devtype", SqlDbType.VarChar, 20);
            sqlpar.Direction = ParameterDirection.Input;
            sqlpar.Value = "disk";

            sqlpar = sqlcmd2.Parameters.Add("@logicalname", SqlDbType.VarChar, 20);//逻辑设备名
            sqlpar.Direction = ParameterDirection.Input;
            sqlpar.Value = databaseName;

            sqlpar = sqlcmd2.Parameters.Add("@physicalname", SqlDbType.NVarChar, 260);//物理设备名
            sqlpar.Direction = ParameterDirection.Input;
            sqlpar.Value = newFilePath;

            try
            {
                int i = sqlcmd2.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                targetDatabaseConn.Close();
                throw err;
            }
            #endregion

            #region 备份数据库到指定的数据库文件(完全备份)
            sql = "BACKUP DATABASE " + databaseName + " TO " + databaseName + " WITH INIT";
            SqlCommand sqlcmd3 = new SqlCommand(sql, targetDatabaseConn);
            sqlcmd3.CommandType = CommandType.Text;
            try
            {
                sqlcmd3.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                targetDatabaseConn.Close();
                throw err;
            }
            #endregion

            targetDatabaseConn.Close();//关闭数据库连接
            return newFilePath;
        }

        /// <summary>
        /// 还原指定的数据库文件
        /// </summary>
        /// <param name="targetDatabaseConn">待还原数据库的连接</param>
        /// <param name="databaseFile">数据库备份文件及全路径</param>
        /// <returns></returns>
        /// <remarks>
        /// 必须使用目标数据库的连接字符串。如需要还原uoLibDb，则conn中的数据库必须是uoLibDb。
        /// </remarks>
        public static bool Restore(SqlConnection targetDatabaseConn, string databaseFile)
        {
            return Restore(targetDatabaseConn, targetDatabaseConn.Database, databaseFile);
        }
        /// <summary>
        /// 还原指定的数据库文件
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <param name="targetDatabaseName">要还原的数据库</param>
        /// <param name="databaseFile">数据库备份文件及全路径</param>
        /// <returns></returns>
        public static bool Restore(SqlConnection conn, string targetDatabaseName, string databaseFile)
        {
            //清空与指定连接关联的连接池。
            SqlConnection.ClearPool(conn);
            conn.Open();

            //（1）使远程数据库转入单用户模式，断开所有已连接数据库的用户的连接并回退它们的事务。 
            //（2）使用T-SQL中的“restore”命令恢复远程数据库。 
            //（3）使远程数据库转入多用户模式。
            //string sql = string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK immediate;"
            //                            + "USE MASTER RESTORE DATABASE [{0}] FROM DISK='{1}' WITH REPLACE;"
            //                            + "ALTER DATABASE [{0}] SET MULTI_USER;", targetDatabaseName, databaseFile);

            string sql = string.Format("ALTER DATABASE [{0}] Set Offline with Rollback immediate;"
                            + "USE MASTER RESTORE DATABASE [{0}] FROM DISK='{1}' WITH REPLACE;"
                            + "ALTER DATABASE [{0}] Set OnLine With rollback Immediate;", targetDatabaseName, databaseFile);

            SqlCommand sqlcmd = new SqlCommand(sql, conn);
            sqlcmd.CommandType = CommandType.Text;

            try
            {
                sqlcmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                //uoLib.Functions.RecordErrors(err);
                conn.Close();
                throw err;
            }

            conn.Close();//关闭数据库连接
            return true;
        }


        #endregion

        #region 生成连接字符串
        public static string CreateExampleConnectionString(string sqlServer, string database, string user, string password)
        {
            return Database.CreateExampleConnectionString(sqlServer, database, user, password, ConnectionType.SqlClient);
        }

        public static string GetSupportedConnType()
        {
            return Database.GetSupportedConnType(DatabaseType.SQL);
        }
        #endregion
    }
}
